package com.ziyun.erp.common.utils;



import com.ziyun.erp.modules.material.entity.storeStockMaterialEntity;
import org.apache.poi.xssf.usermodel.*;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @Author ZhaoWei
 * @Date 2018/11/06 16:53   导入Excel 工具类
 * @Version V1.0
 */
public class UploadExcel {

	public static XSSFWorkbook getExcel(String sheetName, List<Map<String, String>> list, String[] titles,
										String[] fieldNames) {
		XSSFWorkbook  wb = new XSSFWorkbook ();
		try {
			XSSFSheet sheet = null;
			// 对每个表生成一个新的sheet,并以表名命名
			if (sheetName == null) {
				sheetName = "sheet1";
			}
			sheet = wb.createSheet(sheetName);

			// 设置表头的说明
			XSSFRow topRow = sheet.createRow(0);
			// 设置列宽
			for (int i = 0; i < titles.length; i++) {
				setCellValue(topRow.createCell((short) i), titles[i]);
			}

			int k = 1;
			for (Map<String, String> map : list) {
				XSSFRow row = sheet.createRow(k);
				for (int i = 0; i < fieldNames.length; i++) {
					setCellValue(row.createCell((short) i), map.get(fieldNames[i]));
				}
				k++;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return wb;
	}

	// 读取excel文件
	public static List<storeStockMaterialEntity> readExcel(InputStream inputStream) throws Exception {
		List<storeStockMaterialEntity> wxOrders = new ArrayList<storeStockMaterialEntity>();
		XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);
		if (xssfWorkbook == null) {
			throw new Exception("未读取到内容,请检查路径！");
		}
		// 遍历xlsx中的sheet
		for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
			XSSFSheet XSSSheet = xssfWorkbook.getSheetAt(numSheet);
			if (XSSSheet == null) {
				continue;
			}
			// 对于每个sheet，读取其中的每一行
			for (int rowNum = 1; rowNum <= XSSSheet.getLastRowNum(); rowNum++) {
				XSSFRow XSSRow = XSSSheet.getRow(rowNum);
				if (XSSRow == null){
					continue;
				}
				storeStockMaterialEntity storeStockMaterialEntity = new storeStockMaterialEntity();
				for(short columnNum = 0 ; columnNum< 7 ; columnNum++){
					XSSFCell cell = XSSRow.getCell(columnNum);
					switch (columnNum){
						case 0:
							//仓库Id
							storeStockMaterialEntity.setStockId(Integer.parseInt((Trim_str(getValue(cell)))));break;
						case 1:
							//耗材ID
							storeStockMaterialEntity.setMaterialId(Trim_str(getValue(cell) ));break;
						case 2:
							//耗材库存
							String total = Trim_str(getValue(cell));
							BigDecimal bd = new BigDecimal(total);
							storeStockMaterialEntity.setMaterialQuantity(bd);
							break;
						case 3:
							// 成本
							String cost = Trim_str(getValue(cell));
							BigDecimal ct = new BigDecimal(cost);
							storeStockMaterialEntity.setCost(ct);
							break;
						case 4:
							//库存金额
							String money = Trim_str(getValue(cell));
							BigDecimal my = new BigDecimal(money);
							storeStockMaterialEntity.setMoney(my);
							break;
						case 5:
							//单位
							storeStockMaterialEntity.setUnit(Trim_str(getValue(cell)));
							break;
						case 6:
							//备注
							storeStockMaterialEntity.setRemark("初次系统导入数据");
							break;
						default:
							return null;
					}

				}
				wxOrders.add(storeStockMaterialEntity);
			}
		}

		return wxOrders;
	}

	// 字符串修剪 去除所有空白符号 ， 问号 ， 中文空格
	static private String Trim_str(String str) {
		if (str == null){
			return null;
		}
		return str.replaceAll("[\\s\\?]", "").replace("　", "");
	}

	private static String getValue(XSSFCell xssfCell) throws Exception {
		if (xssfCell == null) {
			throw new Exception("除了Remark,其它字段不能为空!");
		}
		if (xssfCell.getCellType() == xssfCell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(xssfCell.getBooleanCellValue());
		} else if (xssfCell.getCellType() == xssfCell.CELL_TYPE_NUMERIC) {
			double cur = xssfCell.getNumericCellValue();
			long longVal = Math.round(cur);
			Object inputValue = null;
			if (Double.parseDouble(longVal + ".0") == cur){
				inputValue = longVal;
			}
			else{
				inputValue = cur;
			}
			return String.valueOf(inputValue);
		} else if (xssfCell.getCellType() == xssfCell.CELL_TYPE_BLANK
				|| xssfCell.getCellType() == xssfCell.CELL_TYPE_ERROR) {
			return "---";
		} else {
			return String.valueOf(xssfCell.getStringCellValue());
		}
	}

	private static void setCellValue(XSSFCell cell, String value) {
		cell.setCellValue(new XSSFRichTextString(value));
	}

	public static void main(String[] args) {
		try {
			File file = new File("D:/IDEAWorkplace/Test.xlsx");
			InputStream inputStream = new FileInputStream(file);
			List<storeStockMaterialEntity> list = UploadExcel.readExcel(inputStream);
			for (int i = 0; i < list.size(); i++) {
				System.out.println(list.get(i));
			}
		} catch (Exception e) {
			e.printStackTrace();

		}

	}

}
